package com.iengoo.inductionms.common;

import com.iengoo.inductionms.entity.Employee;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class PayExcel {
    public static void export(String path, String name, List<Employee> employees) throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();

        HSSFSheet sheet = wb.createSheet();
        sheet.setDefaultColumnWidth(20);//自定义设置列宽和行高
        sheet.setDefaultRowHeightInPoints(20);

        /**
         * 字体设置
         */
        HSSFFont font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 10);//设置字体大小
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗

        /**
         * 设置边框，单元格居中
         */
        HSSFCellStyle style = wb.createCellStyle();


        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setFont(font);

        /**
         * 单元格背景为黄色
         */
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//设置背景色
        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//居中
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);//下边框
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);//右边框
        cellStyle.setFont(font);

        HSSFRow row = sheet.createRow(0);
        HSSFRow row1 = sheet.createRow(1);

        //合并的单元格样式
        HSSFCellStyle boderStyle = wb.createCellStyle();
        //垂直居中
        boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        //设置一个边框
        boderStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
        boderStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        boderStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//上边框
        boderStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//右边框
        boderStyle.setFont(font);

        sheet.addMergedRegion(new CellRangeAddress(
                0,//起始行
                1,//结束行
                0,//起始列
                0//结束列

        ));//合并单元格
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("序号");
        cell.setCellStyle(boderStyle);
        sheet.addMergedRegion(new CellRangeAddress(
                0,//起始行
                1,//结束行
                1,//起始列
                1//结束列

        ));//合并单元格
        HSSFCell cell1 = row.createCell(1);
        cell1.setCellStyle(boderStyle);
        cell1.setCellValue("姓名");
        sheet.addMergedRegion(new CellRangeAddress(
                0,//起始行
                1,//结束行
                2,//起始列
                2//结束列

        ));//合并单元格
        HSSFCell cell2 = row.createCell(2);
        cell2.setCellValue("员工编号");
        cell2.setCellStyle(boderStyle);
        sheet.addMergedRegion(new CellRangeAddress(
                0,//起始行
                0,//结束行
                3,//起始列
                10//结束列

        ));//合并单元格
        HSSFCell cell3 = row.createCell(3);
        cell3.setCellStyle(style);
        cell3.setCellValue("基本信息");

        HSSFCell cell03 = row1.createCell(3);
        cell03.setCellValue("身份证号码");
        cell03.setCellStyle(style);

        HSSFCell cell4 = row1.createCell(4);
        cell4.setCellValue("性别");
        cell4.setCellStyle(style);

        HSSFCell cell5 = row1.createCell(5);
        cell5.setCellValue("移动电话");
        cell5.setCellStyle(style);

        HSSFCell cell6 = row1.createCell(6);
        cell6.setCellValue("参加工作时间");
        cell6.setCellStyle(style);

        HSSFCell cell7 = row1.createCell(7);
        cell7.setCellValue("进入本单位时间");
        cell7.setCellStyle(style);

        HSSFCell cell8 = row1.createCell(8);
        cell8.setCellValue("员工类型");
        cell8.setCellStyle(style);

        HSSFCell cell9 = row1.createCell(9);
        cell9.setCellValue("用工性质");
        cell9.setCellStyle(style);

        HSSFCell cell10 = row1.createCell(10);
        cell10.setCellValue("来源");
        cell10.setCellStyle(style);


        sheet.addMergedRegion(new CellRangeAddress(
                0,//起始行
                0,//结束行
                11,//起始列
                15//结束列

        ));//合并单元格
        HSSFCell cell11 = row.createCell(11);
        cell11.setCellStyle(style);
        cell11.setCellValue("薪资信息");

        HSSFCell row011 = row1.createCell(11);
        row011.setCellStyle(cellStyle);
        row011.setCellValue("职位级别");

        HSSFCell row012 = row1.createCell(12);
        row012.setCellStyle(cellStyle);
        row012.setCellValue("职等");

        HSSFCell row013 = row1.createCell(13);
        row013.setCellStyle(style);
        row013.setCellValue("户名");

        HSSFCell row014 = row1.createCell(14);
        row014.setCellStyle(style);
        row014.setCellValue("开户行");

        HSSFCell row015 = row1.createCell(15);
        row015.setCellStyle(style);
        row015.setCellValue("银行账号");

        sheet.addMergedRegion(new CellRangeAddress(
                0,//起始行
                0,//结束行
                16,//起始列
                19//结束列
        ));//合并单元格
        HSSFCell cell16 = row.createCell(16);
        cell16.setCellStyle(style);
        cell16.setCellValue("社保信息");

        HSSFCell row016 = row1.createCell(16);
        row016.setCellStyle(style);
        row016.setCellValue("原社保公积金所在地");

        HSSFCell row017 = row1.createCell(17);
        row017.setCellStyle(style);
        row017.setCellValue("原单位社保缴费基数");

        HSSFCell row018 = row1.createCell(18);
        row018.setCellStyle(style);
        row018.setCellValue("原单位社保停保时间");

        HSSFCell row019 = row1.createCell(19);
        row019.setCellStyle(cellStyle);
        row019.setCellValue("社保公积金新增开始时间");

        createRows(sheet, employees);


        FileOutputStream fileOut = new FileOutputStream(path + "salary.xls");
        wb.write(fileOut);
        fileOut.close();

    }

    public static void createRows(Sheet sheet, List<Employee> employees) {
        for (int i = 0; i < employees.size(); i++) {
            Row row = sheet.createRow(i + 2);
            Employee e = employees.get(i);

            Cell cell = row.createCell(0);
            cell.setCellValue(nullToBlank(String.valueOf(e.getId())));

            Cell cell1 = row.createCell(1);
            cell1.setCellValue(nullToBlank(e.getEmployeeName()));

            Cell cell2 = row.createCell(2);
            cell2.setCellValue(nullToBlank(e.getEmployeeId()));

            Cell cell3 = row.createCell(3);
            cell3.setCellValue(nullToBlank(e.getIdcardNo()));


            Cell cell4 = row.createCell(4);
            cell4.setCellValue(nullToBlank(e.getEmployeeGender()));


            Cell cell5 = row.createCell(5);
            cell5.setCellValue(nullToBlank(e.getMobilePhone()));


            Cell cell6 = row.createCell(6);
            cell6.setCellValue(format(e.getWorkTime()));


            Cell cell7 = row.createCell(7);
            cell7.setCellValue(format(e.getEntryTime()));


            Cell cell8 = row.createCell(8);
            cell8.setCellValue(nullToBlank(e.getEmployeeType()));


            Cell cell9 = row.createCell(9);
            cell9.setCellValue(nullToBlank(e.getEmploymentNature()));

            Cell cell10 = row.createCell(10);
            cell10.setCellValue(nullToBlank(e.getEmployeeSource()));

            Cell cell11 = row.createCell(11);
            cell11.setCellValue(nullToBlank(null));

            Cell cell12 = row.createCell(12);
            cell12.setCellValue(nullToBlank(e.getJobRank()));

            Cell cell13 = row.createCell(13);
            cell13.setCellValue(nullToBlank(null));

            Cell cell14 = row.createCell(14);
            cell14.setCellValue(nullToBlank(null));

            Cell cell15 = row.createCell(15);
            cell15.setCellValue(nullToBlank(null));

            Cell cell16 = row.createCell(16);
            cell16.setCellValue(nullToBlank(null));


            Cell cell17 = row.createCell(17);
            cell17.setCellValue(nullToBlank(null));


            Cell cell18 = row.createCell(18);
            cell18.setCellValue(nullToBlank(null));


            Cell cell19 = row.createCell(19);
            cell19.setCellValue(nullToBlank(null));

        }

    }

    public static String nullToBlank(String o) {
        return o == null ? "" : o;
    }

    public static String format(Date date) {
        return date == null ? "" : new SimpleDateFormat("yyyy-MM-dd").format(date);
    }
}
